﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess = Microsoft.MSE.Catalog.Module.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;
using System.Data.SqlClient;
using System.Data;

namespace Microsoft.MSE.Catalog.Module.Business
{
    public class CapabilityManager
    {        

        #region Actor functions

        public static Guid CreateActor(DTO.Actor actor, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Actor.CreateActor(actor, txn, conn);
        }

        public static DTO.Actor GetActor(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DTO.Actor actor = DataAccess.Actor.GetActor(ID, txn, conn);
            DTO.SLE sle = new Microsoft.MSE.Catalog.Contracts.SLE();

            actor.Capabilities = new List<DTO.Capability>();
            actor.SLEs = new Dictionary<Guid, Microsoft.MSE.Catalog.Contracts.SLE>();
            DTO.CapabilityList capList = DataAccess.Actor.ListCapabilitiesForActor(ID, txn, conn);
            if (capList.Capabilities != null)
            {
                foreach (DTO.CapabilityListElement element in capList.Capabilities)
                {
                    try
                    {
                        actor.Capabilities.Add(CapabilityManager.GetCapability(element.ID,txn,conn));
                        sle = CapabilityManager.GetSLEForActorCapability(actor.ID,element.ID,txn,conn);
                        if (sle.ID != Guid.Empty)
                            actor.SLEs.Add(element.ID,sle);
                    }
                    catch (Exception ex)
                    {
                        TraceHelper.TraceError("Capability will not be available and Service Catalog was unable to load the Capability with Name=[{0}] Id=[{1}] due to error [{2}]. ", element.Name, element.ID, TraceHelper.GetExceptionDetails(ex));
                    }
                }
            }

            return actor;
        }

        public static void DeleteActor(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Actor.DeleteActor(ID, txn, conn);
        }

        public static DTO.Actor UpdateActor(DTO.Actor actor, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool newTransaction = false;
            DTO.Actor result = null;

            Guid sysID = DataAccess.Entity.GetIDFromName(actor.Name, Microsoft.MSE.Catalog.Contracts.EntityClass.Actor, txn, conn);
            if (sysID == Guid.Empty || (sysID.CompareTo(actor.ID) == 0))
            {

                try
                {
                    if (conn == null) conn = CatalogSvcHelper.GetConnection();
                    if (conn.State == ConnectionState.Closed)
                    {
                        autoclose = true;
                        conn.Open();
                    }

                    if (txn == null)
                    {
                        txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);
                        newTransaction = true;
                    }

                    //collect mseType information (can be isolated from data modifications)
                    DTO.CapabilityList capList = CapabilityManager.ListCapabilitiesForActor(actor.ID, txn, conn);                    
                   
                    result = DataAccess.Actor.UpdateActor(actor, txn, conn);

                    #region Capability Association changes
                    //remove any Capability associations that should no longer be
                    bool present = false;
                    DTO.SLE curSLE = new Microsoft.MSE.Catalog.Contracts.SLE();
                    if (capList.Capabilities != null)
                    {
                        foreach (DTO.CapabilityListElement cle in capList.Capabilities)
                        {
                            present = false;
                            if (result.Capabilities != null)
                            {
                                foreach (DTO.Capability xcle in result.Capabilities)
                                {
                                    if (cle.ID == xcle.ID)
                                    {
                                        present = true;
                                        curSLE = CapabilityManager.GetSLEForActorCapability(actor.ID, cle.ID, txn, conn);

                                        if (result.SLEs != null && result.SLEs.Count > 0 && result.SLEs.ContainsKey(cle.ID))
                                        {
                                            if (curSLE != null && curSLE.ID != Guid.Empty)
                                            {
                                                if (result.SLEs[cle.ID].ID != curSLE.ID)
                                                    present = false;
                                            }
                                            else
                                                present = false;
                                        }
                                        else
                                            if (curSLE != null || curSLE.ID != Guid.Empty)
                                                present = false;
                                    }                                        
                                }
                            }
                            if (!present)
                            {
                                CapabilityManager.RemoveCapabilityFromActor(result.ID, cle.ID, txn, conn);
                            }
                        }
                    }
                    //Add any new Capability associations
                    capList = CapabilityManager.ListCapabilitiesForActor(actor.ID, txn, conn);
                    if (result.Capabilities != null)
                    {
                        foreach (DTO.Capability xcle in result.Capabilities)
                        {
                            present = false;
                            if (capList.Capabilities != null)
                            {
                                foreach (DTO.CapabilityListElement cle in capList.Capabilities)
                                {
                                    if (cle.ID == xcle.ID)
                                    {
                                        present = true;
                                        break;
                                    }
                                }
                            }
                            if (!present)
                            {
                                if (xcle.ID.Equals(Guid.Empty))
                                {
                                    CapabilityManager.CreateCapability(xcle, txn, conn);
                                }
                                if (result.SLEs != null && result.SLEs.ContainsKey(xcle.ID))
                                    CapabilityManager.AssignCapabilityToActor(result.ID, xcle.ID, result.SLEs[xcle.ID].ID, txn, conn);
                                else
                                    CapabilityManager.AssignCapabilityToActor(result.ID, xcle.ID, Guid.Empty, txn, conn);

                               
                            }
                        }
                    }
                    #endregion

                    if ((autoclose) && (txn != null) && (newTransaction)) txn.Commit();
                }
                catch (Exception)
                {
                    if ((txn != null) && (newTransaction)) txn.Rollback();
                    throw;
                }
                finally
                {
                    if (autoclose && (conn != null)) conn.Close();
                }
            }
            else
            {
                if (txn != null) txn.Rollback(); //Rollback transaction
                throw new ApplicationException("Actor already exists in the Service Model");
            }

            return result;
        }

        public static DTO.ActorList ListActors(SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Actor.ListActors(txn, conn);
        }

        public static DTO.CapabilityList ListCapabilitiesForActor(Guid actorID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Actor.ListCapabilitiesForActor(actorID, txn, conn);
        }

        public static DTO.SLEList ListSLEsForActor(Guid actorID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Actor.ListSLEsForActor(actorID, txn, conn);
        }

        public static void AssignCapabilityToActor(Guid actorID, Guid capabilityID, Guid sleID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Actor.AssignCapabilityToActor(actorID, capabilityID, sleID, txn, conn);
        }

        public static void RemoveCapabilityFromActor(Guid actorID, Guid capabilityID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Actor.RemoveCapabilityFromActor(actorID, capabilityID, txn, conn);
        }


        #region Actor Group functions

        public static List<DTO.ActorListElement> ListActorsByGroup(Guid entityGroupID)
        {
            return DataAccess.Actor.ListActorsByGroup(entityGroupID, null, null);
        }

        public static DTO.ActorGroup CreateActorGroup(DTO.ActorGroup actorGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    actorGroup.ID = DataAccess.Entity.CreateEntityGroup(actorGroup.Name, actorGroup.Description, 23, txn, conn);

                    if (actorGroup.Actors != null)
                    {
                        foreach (DTO.ActorListElement process in actorGroup.Actors)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(actorGroup.ID, process.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return actorGroup;
        }

        public static DTO.ActorGroup GetActorGroup(Guid ID)
        {
            DTO.ActorGroup actorGroup = new DTO.ActorGroup();
            DTO.EntityGroup entityGroup = new DTO.EntityGroup();

            entityGroup = DataAccess.Entity.GetEntityGroup(ID, null, null);

            actorGroup.ID = entityGroup.ID;
            actorGroup.Name = entityGroup.Name;
            actorGroup.Description = entityGroup.Description;

            actorGroup.Actors = ListActorsByGroup(ID);

            return actorGroup;
        }

        public static DTO.ActorGroup UpdateActorGroup(DTO.ActorGroup actorGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {

                    DataAccess.Entity.UpdateEntityGroup(actorGroup.ID, actorGroup.Name, actorGroup.Description, txn, conn);

                    List<DTO.ActorListElement> oldList = DataAccess.Actor.ListActorsByGroup(actorGroup.ID, txn, conn);
                /// Delete all items from the old list
                if (oldList != null)
                {
                    foreach (DTO.ActorListElement oldListElement in oldList)
                    {
                        DataAccess.Entity.RemoveEntityFromEntityGroup(actorGroup.ID, oldListElement.ID, txn, conn);
                    }
                }
                /// Add all items from the new list
                if (actorGroup.Actors != null)
                {
                    foreach (DTO.ActorListElement newListElement in actorGroup.Actors)
                    {
                        DataAccess.Entity.AssignEntityToEntityGroup(actorGroup.ID, newListElement.ID, txn, conn);
                    }
                }

                if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return actorGroup;
        }

        #endregion

        #endregion

        #region Capability functions

        public static Guid CreateCapability(DTO.Capability capability, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Capability.CreateCapability(capability, txn, conn);
        }

        public static DTO.Capability UpdateCapability(DTO.Capability capability, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool newTransaction = false;
            DTO.Capability result = null;

            Guid sysID = DataAccess.Entity.GetIDFromName(capability.Name, Microsoft.MSE.Catalog.Contracts.EntityClass.Capability, txn, conn);
            if (sysID == Guid.Empty || (sysID.CompareTo(capability.ID) == 0))
            {

                try
                {
                    if (conn == null) conn = CatalogSvcHelper.GetConnection();
                    if (conn.State == ConnectionState.Closed)
                    {
                        autoclose = true;
                        conn.Open();
                    }

                    if (txn == null)
                    {
                        txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);
                        newTransaction = true;
                    }

                    //collect mseType information (can be isolated from data modifications)
                    DTO.MSETypeList inputTypeList = CapabilityManager.ListTypesForCapability(capability.ID, false, txn, conn);
                    DTO.MSETypeList outputTypeList = CapabilityManager.ListTypesForCapability(capability.ID, true, txn, conn);

                    //collect operation information (can be isolated from data modifications)
                    DTO.OperationList opList = CapabilityManager.ListOperationsForCapability(capability.ID, txn, conn);

                    result = DataAccess.Capability.UpdateCapability(capability, txn, conn);

                    #region Input MSEType Association changes
                    //remove any mseType associations that should no longer be
                    bool present = false;
                    if (inputTypeList.MSETypes != null)
                    {
                        foreach (DTO.MSETypeListElement tp in inputTypeList.MSETypes)
                        {
                            present = false;
                            if (capability.InputMSETypes != null)
                            {
                                foreach (DTO.MSEType xtp in capability.InputMSETypes)
                                {
                                    if (tp.ID == xtp.ID)
                                        present = true;
                                }
                            }
                            if (!present)
                            {
                                CapabilityManager.RemoveTypeFromCapability(capability.ID,tp.ID, false, txn, conn);
                            }
                        }
                    }
                    //Add any new mseType associations
                    if (capability.InputMSETypes != null)
                    {
                        foreach (DTO.MSEType xtp in capability.InputMSETypes)
                        {
                            present = false;
                            if (inputTypeList.MSETypes != null)
                            {
                                foreach (DTO.MSETypeListElement tp in inputTypeList.MSETypes)
                                {
                                    if (tp.ID == xtp.ID)
                                    {
                                        present = true;
                                        break;
                                    }
                                }
                            }
                            if (!present)
                            {
                                if (xtp.ID.Equals(Guid.Empty))
                                {
                                    xtp.ID = CapabilityManager.CreateMSEType(xtp, txn, conn);
                                }
                                CapabilityManager.AssignTypeToCapability(capability.ID, xtp.ID, false, txn, conn);
                            }
                        }
                    }
                    #endregion

                    #region Output MSEType Association changes
                    //remove any mseType associations that should no longer be                    
                    if (outputTypeList.MSETypes != null)
                    {
                        foreach (DTO.MSETypeListElement tp in outputTypeList.MSETypes)
                        {
                            present = false;
                            if (capability.OutputMSETypes != null)
                            {
                                foreach (DTO.MSEType xtp in capability.OutputMSETypes)
                                {
                                    if (tp.ID == xtp.ID)
                                        present = true;
                                }
                            }
                            if (!present)
                            {
                                CapabilityManager.RemoveTypeFromCapability(capability.ID, tp.ID, true, txn, conn);
                            }
                        }
                    }
                    //Add any new mseType associations
                    if (capability.OutputMSETypes != null)
                    {
                        foreach (DTO.MSEType xtp in capability.OutputMSETypes)
                        {
                            present = false;
                            if (outputTypeList.MSETypes != null)
                            {
                                foreach (DTO.MSETypeListElement tp in outputTypeList.MSETypes)
                                {
                                    if (tp.ID == xtp.ID)
                                    {
                                        present = true;
                                        break;
                                    }
                                }
                            }
                            if (!present)
                            {
                                if (xtp.ID.Equals(Guid.Empty))
                                {
                                    xtp.ID = CapabilityManager.CreateMSEType(xtp, txn, conn);
                                }
                                CapabilityManager.AssignTypeToCapability(capability.ID, xtp.ID, true, txn, conn);
                            }
                        }
                    }
                    #endregion


                    #region Operation Association changes
                    //remove any operation associations that should no longer be
                    present = false;
                    if (opList.Operations != null)
                    {
                        foreach (DTO.OperationListElement op in opList.Operations)
                        {
                            present = false;
                            if (capability.Operations != null)
                            {
                                foreach (DTO.Operation otp in capability.Operations)
                                {
                                    if (op.ID == otp.ID)
                                        present = true;
                                }
                            }
                            if (!present)
                            {
                                CapabilityManager.RemoveOperationFromCapability(capability.ID, op.ID, txn, conn);
                            }
                        }
                    }
                    //Add any new operation associations
                    if (capability.Operations != null)
                    {
                        foreach (DTO.Operation otp in capability.Operations)
                        {
                            present = false;
                            if (opList.Operations != null)
                            {
                                foreach (DTO.OperationListElement op in opList.Operations)
                                {
                                    if (op.ID == otp.ID)
                                    {
                                        present = true;
                                        break;
                                    }
                                }
                            }
                            if (!present)
                            {
                                if (otp.ID.Equals(Guid.Empty))
                                {
                                    otp.ID = OperationManager.CreateOperation(otp);
                                }
                                CapabilityManager.AssignOperationToCapability(capability.ID, otp.ID, txn, conn);
                            }
                        }
                    }
                    #endregion

                    if ((autoclose) && (txn != null) && (newTransaction)) txn.Commit();
                }
                catch (Exception)
                {
                    if ((txn != null) && (newTransaction)) txn.Rollback();
                    throw;
                }
                finally
                {
                    if (autoclose && (conn != null)) conn.Close();
                }
            }
            else
            {
                if (txn != null) txn.Rollback(); //Rollback transaction
                throw new ApplicationException("Actor already exists in the Service Model");
            }

            return result;
        }

        public static void DeleteCapability(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.DeleteCapability(ID, txn, conn);
        }

        public static DTO.Capability GetCapability(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DTO.Capability capability = DataAccess.Capability.GetCapability(ID, txn, conn);

            capability.InputMSETypes = new List<DTO.MSEType>();
            capability.OutputMSETypes = new List<DTO.MSEType>();
            DTO.MSETypeList inputTypeList = CapabilityManager.ListTypesForCapability(ID, false, txn, conn);
            DTO.MSETypeList outputTypeList = CapabilityManager.ListTypesForCapability(ID, true, txn, conn);
            if (inputTypeList.MSETypes != null)
            {
                foreach (DTO.MSETypeListElement element in inputTypeList.MSETypes)
                {
                    try
                    {
                        capability.InputMSETypes.Add(DataAccess.MSEType.GetMSEType(element.ID, txn, conn));
                    }
                    catch (Exception ex)
                    {
                        TraceHelper.TraceError("MSEType will not be available and Service Catalog was unable to load the MSEType with Name=[{0}] Id=[{1}] due to error [{2}]. ", element.Name, element.ID, TraceHelper.GetExceptionDetails(ex));
                    }
                }
            }

            if (outputTypeList.MSETypes != null)
            {
                foreach (DTO.MSETypeListElement element in outputTypeList.MSETypes)
                {
                    try
                    {
                        capability.OutputMSETypes.Add(DataAccess.MSEType.GetMSEType(element.ID, txn, conn));
                    }
                    catch (Exception ex)
                    {
                        TraceHelper.TraceError("MSEType will not be available and Service Catalog was unable to load the MSEType with Name=[{0}] Id=[{1}] due to error [{2}]. ", element.Name, element.ID, TraceHelper.GetExceptionDetails(ex));
                    }
                }
            }

            capability.Operations = new List<DTO.Operation>();
            DTO.OperationList opList = DataAccess.Capability.ListOperationsForCapability(ID, txn, conn);
            if (opList.Operations != null)
            {
                foreach (DTO.OperationListElement element in opList.Operations)
                {
                    try
                    {
                        capability.Operations.Add(OperationManager.GetOperation(element.ID, txn, conn));
                    }
                    catch (Exception ex)
                    {
                        TraceHelper.TraceError("Operation will not be available and Service Catalog was unable to load the Operation with Name=[{0}] Id=[{1}] due to error [{2}]. ", element.Name, element.ID, TraceHelper.GetExceptionDetails(ex));
                    }
                }
            }

            return capability;
        }

        public static DTO.CapabilityList ListCapabilities(SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Capability.ListCapabilities(txn, conn);
        }

        public static DTO.MSETypeList ListTypesForCapability(Guid capabilityID, Boolean outputFlag, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Capability.ListTypesForCapability(capabilityID, outputFlag, txn, conn);
        }

        public static DTO.OperationList ListOperationsForCapability(Guid capabilityID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.Capability.ListOperationsForCapability(capabilityID, txn, conn);
        }

        public static void AssignTypeToCapability(Guid capabilityID, Guid typeID, Boolean outputFlag, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.AssignTypeToCapability(capabilityID, typeID, outputFlag, txn, conn);            
        }

        public static void RemoveTypeFromCapability(Guid capabilityID, Guid typeID, Boolean outputFlag, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.RemoveTypeFromCapability(capabilityID, typeID, outputFlag, txn, conn);
        }

        public static void AssignOperationToCapability(Guid capabilityID, Guid typeID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.AssignOperationToCapability(capabilityID, typeID, txn, conn);
        }

        public static void RemoveOperationFromCapability(Guid capabilityID, Guid typeID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.RemoveOperationFromCapability(capabilityID, typeID, txn, conn);
        }

        public static void AssignCapabilityToCapability(Guid parentCapabilityID, Guid childCapabilityID, Int16 order, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.AssignCapabilityToCapability(parentCapabilityID, childCapabilityID, order, txn, conn);
        }

        public static void RemoveCapabilityFromCapability(Guid parentCapabilityID, Guid childCapabilityID, Int16 order, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.Capability.RemoveCapabilityFromCapability(parentCapabilityID, childCapabilityID, order, txn, conn);
        }


        #region Capability Group functions

        public static List<DTO.CapabilityListElement> ListCapabilitiesByGroup(Guid entityGroupID)
        {
            return DataAccess.Capability.ListCapabilitiesByGroup(entityGroupID, null, null);
        }

        public static DTO.CapabilityGroup CreateCapabilityGroup(DTO.CapabilityGroup capabilityGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    capabilityGroup.ID = DataAccess.Entity.CreateEntityGroup(capabilityGroup.Name, capabilityGroup.Description, 24, txn, conn);

                    if (capabilityGroup.Capabilities != null)
                    {
                        foreach (DTO.CapabilityListElement capability in capabilityGroup.Capabilities)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(capabilityGroup.ID, capability.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return capabilityGroup;
        }

        public static DTO.CapabilityGroup GetCapabilityGroup(Guid ID)
        {
            DTO.CapabilityGroup capabilityGroup = new DTO.CapabilityGroup();
            DTO.EntityGroup entityGroup = new DTO.EntityGroup();

            entityGroup = DataAccess.Entity.GetEntityGroup(ID, null, null);

            capabilityGroup.ID = entityGroup.ID;
            capabilityGroup.Name = entityGroup.Name;
            capabilityGroup.Description = entityGroup.Description;

            capabilityGroup.Capabilities = ListCapabilitiesByGroup(ID);

            return capabilityGroup;
        }

        public static DTO.CapabilityGroup UpdateCapabilityGroup(DTO.CapabilityGroup capabilityGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {

                    DataAccess.Entity.UpdateEntityGroup(capabilityGroup.ID, capabilityGroup.Name, capabilityGroup.Description, txn, conn);

                    List<DTO.CapabilityListElement> oldList = DataAccess.Capability.ListCapabilitiesByGroup(capabilityGroup.ID, txn, conn);
                    /// Delete all items from the old list
                    if (oldList != null)
                    {
                        foreach (DTO.CapabilityListElement oldListElement in oldList)
                        {
                            DataAccess.Entity.RemoveEntityFromEntityGroup(capabilityGroup.ID, oldListElement.ID, txn, conn);
                        }
                    }
                    /// Add all items from the new list
                    if (capabilityGroup.Capabilities != null)
                    {
                        foreach (DTO.CapabilityListElement newListElement in capabilityGroup.Capabilities)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(capabilityGroup.ID, newListElement.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return capabilityGroup;
        }

        #endregion

        #endregion       

        #region MSEType functions

        public static Guid CreateMSEType(DTO.MSEType mseType, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.MSEType.CreateMSEType(mseType, txn, conn);
        }

        public static DTO.MSEType GetMSEType(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DTO.MSEType mseType = DataAccess.MSEType.GetMSEType(ID, txn, conn);

            mseType.DataEntities = new List<DTO.DataEntity>();
            DTO.DataEntityList deList = DataAccess.MSEType.ListDataEntitiesForMSEType(ID, txn, conn);
            if (deList.DataEntities != null)
            {
                foreach (DTO.DataEntityListElement element in deList.DataEntities)
                {
                    try
                    {
                        mseType.DataEntities.Add(DataEntityManager.GetDataEntity(element.ID));
                    }
                    catch (Exception ex)
                    {
                        TraceHelper.TraceError("DataEntity will not be available and Service Catalog was unable to load the DataEntity with Name=[{0}] Id=[{1}] due to error [{2}]. ", element.Name, element.ID, TraceHelper.GetExceptionDetails(ex));
                    }
                }
            }

            return mseType;
        }

        public static void DeleteMSEType(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.MSEType.DeleteMSEType(ID, txn, conn);
        }

        public static DTO.MSEType UpdateMSEType(DTO.MSEType mseType, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool newTransaction = false;
            DTO.MSEType result = null;

            Guid typeID = DataAccess.Entity.GetIDFromName(mseType.Name, Microsoft.MSE.Catalog.Contracts.EntityClass.Type, txn, conn);
            if (typeID == Guid.Empty || (typeID.CompareTo(mseType.ID) == 0)) 
            {
                try
                {
                    if (conn == null) conn = CatalogSvcHelper.GetConnection();
                    if (conn.State == ConnectionState.Closed)
                    {
                        autoclose = true;
                        conn.Open();
                    }

                    if (txn == null)
                    {
                        txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);
                        newTransaction = true;
                    }

                    //collect DataEntity information (can be isolated from data modifications)
                    DTO.DataEntityList deList = CapabilityManager.ListDataEntitiesForMSEType(mseType.ID, txn, conn);                    

                    result = DataAccess.MSEType.UpdateMSEType(mseType, txn, conn);

                    #region DataEntity Association changes
                    //remove any DataEntity associations that should no longer be
                    bool present = false;
                    if (deList.DataEntities != null)
                    {
                        foreach (DTO.DataEntityListElement de in deList.DataEntities)
                        {
                            present = false;
                            if (result.DataEntities != null)
                            {
                                foreach (DTO.DataEntity xde in result.DataEntities)
                                {
                                    if (de.ID == xde.ID)
                                        present = true;
                                }
                            }
                            if (!present)
                            {
                                CapabilityManager.RemoveDataEntityFromMSEType(mseType.ID, de.ID, txn, conn);
                            }
                        }
                    }
                    //Add any new DataEntity associations
                    if (mseType.DataEntities != null)
                    {
                        foreach (DTO.DataEntity xde in mseType.DataEntities)
                        {
                            present = false;
                            if (deList.DataEntities != null)
                            {
                                foreach (DTO.DataEntityListElement de in deList.DataEntities)
                                {
                                    if (de.ID == xde.ID)
                                    {
                                        present = true;
                                        break;
                                    }
                                }
                            }
                            if (!present)
                            {
                                if (xde.ID.Equals(Guid.Empty))
                                {
                                    DataEntityManager.CreateDataEntity(xde, txn, conn, true);
                                }
                                CapabilityManager.AssignDataEntityToMSEType(mseType.ID, xde.ID, txn, conn);
                            }
                        }
                    }
                    #endregion
                    
                    if ((autoclose) && (txn != null) && (newTransaction)) txn.Commit();
                }
                catch (Exception)
                {
                    if ((txn != null) && (newTransaction)) txn.Rollback();
                    throw;
                }
                finally
                {
                    if (autoclose && (conn != null)) conn.Close();
                }
            }
            else
            {
                if (txn != null) txn.Rollback(); //Rollback transaction
                throw new ApplicationException("Eror updating Type in the Service Model");
            }

            return result;

        }
        
        public static DTO.MSETypeList ListMSETypes( SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.MSEType.ListMSETypes(txn, conn);
        }

        public static DTO.DataEntityList ListDataEntitiesForMSEType(Guid mseTypeID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.MSEType.ListDataEntitiesForMSEType(mseTypeID, txn, conn);
        }

        public static void AssignDataEntityToMSEType(Guid mseTypeID, Guid dataEntityID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.MSEType.AssignDataEntityToMSEType(mseTypeID, dataEntityID, txn, conn);
        }

        public static void RemoveDataEntityFromMSEType(Guid mseTypeID, Guid dataEntityID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.MSEType.RemoveDataEntityFromMSEType(mseTypeID, dataEntityID, txn, conn);
        }


        #region MSEType Group functions

        public static DTO.MSETypeGroup CreateTypeGroup(DTO.MSETypeGroup typeGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    typeGroup.ID = DataAccess.Entity.CreateEntityGroup(typeGroup.Name, typeGroup.Description, 25, txn, conn);

                    if (typeGroup.Types != null)
                    {
                        foreach (DTO.MSETypeListElement type in typeGroup.Types)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(typeGroup.ID, type.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return typeGroup;
        }

        public static DTO.MSETypeGroup GetTypeGroup(Guid ID)
        {
            DTO.MSETypeGroup typeGroup = new DTO.MSETypeGroup();
            DTO.EntityGroup entityGroup = new DTO.EntityGroup();

            entityGroup = DataAccess.Entity.GetEntityGroup(ID, null, null);

            typeGroup.ID = entityGroup.ID;
            typeGroup.Name = entityGroup.Name;
            typeGroup.Description = entityGroup.Description;

            typeGroup.Types = ListTypesByGroup(ID);

            return typeGroup;
        }

        public static DTO.MSETypeGroup UpdateTypeGroup(DTO.MSETypeGroup typeGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    DataAccess.Entity.UpdateEntityGroup(typeGroup.ID, typeGroup.Name, typeGroup.Description, txn, conn);

                    List<DTO.MSETypeListElement> oldList = DataAccess.MSEType.ListMSETypesByGroup(typeGroup.ID, txn, conn);
                    /// Delete all items from the old list
                    if (oldList != null)
                    {
                        foreach (DTO.MSETypeListElement oldListElement in oldList)
                        {
                            DataAccess.Entity.RemoveEntityFromEntityGroup(typeGroup.ID, oldListElement.ID, txn, conn);
                        }
                    }
                    /// Add all items from the new list
                    if (typeGroup.Types != null)
                    {
                        foreach (DTO.MSETypeListElement newListElement in typeGroup.Types)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(typeGroup.ID, newListElement.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return typeGroup;
        }

        public static List<DTO.MSETypeListElement> ListTypesByGroup(Guid entityGroupID)
        {
            return DataAccess.MSEType.ListMSETypesByGroup(entityGroupID, null, null);
        }

        #endregion

        #endregion

        #region SLE functions

        public static Guid CreateSLE(DTO.SLE sLE, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.SLE.CreateSLE(sLE, txn, conn);
        }

        public static DTO.SLE GetSLE(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.SLE.GetSLE(ID, txn, conn);
        }

        public static void DeleteSLE(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            DataAccess.SLE.DeleteSLE(ID, txn, conn);
        }

        public static DTO.SLE UpdateSLE(DTO.SLE sLE, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool newTransaction = false;
            DTO.SLE result = null;

            Guid sLEID = DataAccess.Entity.GetIDFromName(sLE.Name, Microsoft.MSE.Catalog.Contracts.EntityClass.SLE, txn, conn);
            if (sLEID == Guid.Empty || (sLEID.CompareTo(sLE.ID) == 0))
            {
                try
                {
                    if (conn == null) conn = CatalogSvcHelper.GetConnection();
                    if (conn.State == ConnectionState.Closed)
                    {
                        autoclose = true;
                        conn.Open();
                    }

                    if (txn == null)
                    {
                        txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);
                        newTransaction = true;
                    }

                    result = DataAccess.SLE.UpdateSLE(sLE, txn, conn);

                    if ((autoclose) && (txn != null) && (newTransaction)) txn.Commit();
                }
                catch (Exception)
                {
                    if ((txn != null) && (newTransaction)) txn.Rollback();
                    throw;
                }
                finally
                {
                    if (autoclose && (conn != null)) conn.Close();
                }
            }
            else
            {
                if (txn != null) txn.Rollback(); //Rollback transaction
                throw new ApplicationException("Eror updating SLE in the Service Model");
            }

            return result;

        }

        public static DTO.SLEList ListSLEs(SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.SLE.ListSLEs(txn, conn);
        }

        public static DTO.SLE GetSLEForActorCapability(Guid actorID, Guid capabilityID, SqlTransaction txn, SqlConnection conn)
        {
            return DataAccess.SLE.GetSLEForActorCapability(actorID, capabilityID, txn, conn);
        }

        #region SLE Group functions

        public static DTO.SLEGroup CreateSLEGroup(DTO.SLEGroup sLEGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    sLEGroup.ID = DataAccess.Entity.CreateEntityGroup(sLEGroup.Name, sLEGroup.Description, 26, txn, conn);

                    if (sLEGroup.SLEs != null)
                    {
                        foreach (DTO.SLEListElement sLE in sLEGroup.SLEs)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(sLEGroup.ID, sLE.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return sLEGroup;
        }

        public static DTO.SLEGroup GetSLEGroup(Guid ID)
        {
            DTO.SLEGroup sLEGroup = new DTO.SLEGroup();
            DTO.EntityGroup entityGroup = new DTO.EntityGroup();

            entityGroup = DataAccess.Entity.GetEntityGroup(ID, null, null);

            sLEGroup.ID = entityGroup.ID;
            sLEGroup.Name = entityGroup.Name;
            sLEGroup.Description = entityGroup.Description;

            sLEGroup.SLEs = ListSLEsByGroup(ID);

            return sLEGroup;
        }

        public static DTO.SLEGroup UpdateSLEGroup(DTO.SLEGroup sLEGroup)
        {
            using (SqlConnection conn = CatalogSvcHelper.GetConnection())
            {
                conn.Open();
                SqlTransaction txn = CatalogSvcHelper.GetTransaction(conn, IsolationLevel.Snapshot);

                try
                {
                    DataAccess.Entity.UpdateEntityGroup(sLEGroup.ID, sLEGroup.Name, sLEGroup.Description, txn, conn);

                    List<DTO.SLEListElement> oldList = DataAccess.SLE.ListSLEsByGroup(sLEGroup.ID, txn, conn);
                    /// Delete all items from the old list
                    if (oldList != null)
                    {
                        foreach (DTO.SLEListElement oldListElement in oldList)
                        {
                            DataAccess.Entity.RemoveEntityFromEntityGroup(sLEGroup.ID, oldListElement.ID, txn, conn);
                        }
                    }
                    /// Add all items from the new list
                    if (sLEGroup.SLEs != null)
                    {
                        foreach (DTO.SLEListElement newListElement in sLEGroup.SLEs)
                        {
                            DataAccess.Entity.AssignEntityToEntityGroup(sLEGroup.ID, newListElement.ID, txn, conn);
                        }
                    }

                    if (txn != null) txn.Commit(); //Commit transaction
                }
                catch (Exception)
                {
                    if (txn != null) txn.Rollback(); //Rollback transaction
                    throw;
                }
            } //Connection is disposed here

            return sLEGroup;
        }

        public static List<DTO.SLEListElement> ListSLEsByGroup(Guid entityGroupID)
        {
            return DataAccess.SLE.ListSLEsByGroup(entityGroupID, null, null);
        }

        #endregion

        #endregion
    }
}
